﻿
-- NON-HEAP TABLES VIEW
CREATE VIEW [todoc].[svNonHeapTables]
AS
/*
LISTS ALL THE TABLES IN THE DATABASE THAT ARE NOT HEAPS
(AND ALSO NOT TODOC SHADOW/AUDIT TABLES)

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
SELECT	ss.[name] AS TableSchema,
		st.[name] AS TableName,
		st.[schema_id],
		st.[object_id]
FROM sys.key_constraints kc
INNER JOIN sys.tables st
	ON kc.parent_object_id = st.[object_id]
INNER JOIN sys.schemas ss
	ON st.[schema_id] = ss.[schema_id]
WHERE kc.[type] = 'PK'
	AND ss.[name] NOT IN('dbo', 'todoc')
	AND st.[name] NOT LIKE '%' + 
		(SELECT CONVERT(nvarchar(128), ep.[value]) FROM sys.extended_properties ep
		WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
			AND ep.[name] = 'TODOC: Table name postfix');